<?php

namespace Home\Service;

use Home\Common\FIdConst;

require __DIR__ . '/../Common/Excel/PHPExcel/IOFactory.php';

/**
 * 库存 Service
 *
 * @author 李静波
 */
class InventoryService extends PSIBaseService {

	public function warehouseList() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$sql = "select id, code, name from t_warehouse ";
		$queryParams = array();
		
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::INVENTORY_QUERY, "t_warehouse");
		if ($rs) {
			$sql .= " where " . $rs[0];
			$queryParams = $rs[1];
		}
		
		$sql .= " order by code";
		
		return M()->query($sql, $queryParams);
	}

	public function inventoryList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$warehouseId = $params["warehouseId"];
		$code = $params["code"];
		$name = $params["name"];
		$spec = $params["spec"];
		$page = $params["page"];
		$start = $params["start"];
		$limit = $params["limit"];
		
		$db = M();
		$queryParams = array();
		$queryParams[] = $warehouseId;
		
		$sql = "select g.id, g.code, g.name, g.spec, u.name as unit_name,
				 v.in_count, v.in_price, v.in_money, v.out_count, v.out_price, v.out_money,
				 v.balance_count, v.balance_price, v.balance_money, v.afloat_count,
					v.afloat_money, v.afloat_price, v.begin_dt, v.end_dt, v.expiration
				from t_inventory_lot v, t_goods g, t_goods_unit u
				where (v.warehouse_id = '%s') and (v.goods_id = g.id) and (g.unit_id = u.id) 
				 ";
		if ($code) {
			$sql .= " and (g.code like '%s')";
			$queryParams[] = "%{$code}%";
		}
		if ($name) {
			$sql .= " and (g.name like '%s' or g.py like '%s')";
			$queryParams[] = "%{$name}%";
			$queryParams[] = "%{$name}%";
		}
		if ($spec) {
			$sql .= " and (g.spec like '%s')";
			$queryParams[] = "%{$spec}%";
		}
		$sql .= " order by g.code
				limit %d, %d";
		$queryParams[] = $start;
		$queryParams[] = $limit;
		
		$data = $db->query($sql, $queryParams);
		
		$result = array();
		
		foreach ( $data as $i => $v ) {
			$result[$i]["goodsId"] = $v["id"];
			$result[$i]["goodsCode"] = $v["code"];
			$result[$i]["goodsName"] = $v["name"];
			$result[$i]["goodsSpec"] = $v["spec"];
			$result[$i]["unitName"] = $v["unit_name"];
			$result[$i]["inCount"] = $v["in_count"];
			$result[$i]["inPrice"] = $v["in_price"];
			$result[$i]["inMoney"] = $v["in_money"];
			$result[$i]["outCount"] = $v["out_count"];
			$result[$i]["outPrice"] = $v["out_price"];
			$result[$i]["outMoney"] = $v["out_money"];
			$result[$i]["balanceCount"] = $v["balance_count"];
			$result[$i]["balancePrice"] = $v["balance_price"];
			$result[$i]["balanceMoney"] = $v["balance_money"];
			$result[$i]["afloatCount"] = $v["afloat_count"];
			$result[$i]["afloatPrice"] = $v["afloat_price"];
			$result[$i]["afloatMoney"] = $v["afloat_money"];
			
			$qcBeginDT = $this->toYMD($v["begin_dt"]);
			if ($qcBeginDT && $qcBeginDT != "1970-01-01") {
				$result[$i]["qcBeginDT"] = $qcBeginDT;
			}
			
			$qcEndDT = $this->toYMD($v["end_dt"]);
			if ($qcEndDT && $qcEndDT != "1970-01-01") {
				$result[$i]["qcEndDT"] = $qcEndDT;
			}
			
			$expiration = $v["expiration"];
			if ($expiration > 0) {
				$result[$i]["expiration"] = $expiration;
			}
		}
		
		$queryParams = array();
		$queryParams[] = $warehouseId;
		$sql = "select count(*) as cnt 
				from t_inventory_lot v, t_goods g, t_goods_unit u
				where (v.warehouse_id = '%s') and (v.goods_id = g.id) and (g.unit_id = u.id) ";
		if ($code) {
			$sql .= " and (g.code like '%s')";
			$queryParams[] = "%{$code}%";
		}
		if ($name) {
			$sql .= " and (g.name like '%s' or g.py like '%s')";
			$queryParams[] = "%{$name}%";
			$queryParams[] = "%{$name}%";
		}
		if ($spec) {
			$sql .= " and (g.spec like '%s')";
			$queryParams[] = "%{$spec}%";
		}
		
		$data = $db->query($sql, $queryParams);
		$cnt = $data[0]["cnt"];
		
		return array(
				"dataList" => $result,
				"totalCount" => $cnt
		);
	}

	public function inventoryDetailList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$warehouseId = $params["warehouseId"];
		$goodsId = $params["goodsId"];
		$beginDT = $params["beginDT"];
		if (! $beginDT) {
			$beginDT = "1970-01-01";
		}
		$expiration = $params["expiration"];
		if (! $expiration) {
			$expiration = 0;
		}
		$dtFrom = $params["dtFrom"];
		$dtTo = $params["dtTo"];
		$page = $params["page"];
		$start = $params["start"];
		$limit = $params["limit"];
		
		$db = M();
		$sql = "select g.id, g.code, g.name, g.spec, u.name as unit_name, 
				v.in_count, v.in_price, v.in_money, v.out_count, v.out_price, v.out_money,
				v.balance_count, v.balance_price, v.balance_money,
				v.biz_date,  user.name as biz_user_name, v.ref_number, v.ref_type,
				v.begin_dt, v.end_dt, v.expiration
				from t_inventory_detail_lot v, t_goods g, t_goods_unit u, t_user user
				where v.warehouse_id = '%s' and v.goods_id = '%s' 
					and v.goods_id = g.id and g.unit_id = u.id 
					and v.biz_user_id = user.id 
					and (v.biz_date between '%s' and '%s' ) 
					and (v.begin_dt = '%s' and v.expiration = %d)
				order by v.id 
				limit %d, %d ";
		$data = $db->query($sql, $warehouseId, $goodsId, $dtFrom, $dtTo, $beginDT, $expiration, 
				$start, $limit);
		
		$result = array();
		
		foreach ( $data as $i => $v ) {
			$result[$i]["goodsId"] = $v["id"];
			$result[$i]["goodsCode"] = $v["code"];
			$result[$i]["goodsName"] = $v["name"];
			$result[$i]["goodsSpec"] = $v["spec"];
			$result[$i]["unitName"] = $v["unit_name"];
			$result[$i]["inCount"] = $v["in_count"];
			$result[$i]["inPrice"] = $v["in_price"];
			$result[$i]["inMoney"] = $v["in_money"];
			$result[$i]["outCount"] = $v["out_count"];
			$result[$i]["outPrice"] = $v["out_price"];
			$result[$i]["outMoney"] = $v["out_money"];
			$result[$i]["balanceCount"] = $v["balance_count"];
			$result[$i]["balancePrice"] = $v["balance_price"];
			$result[$i]["balanceMoney"] = $v["balance_money"];
			$result[$i]["bizDT"] = date("Y-m-d", strtotime($v["biz_date"]));
			$result[$i]["bizUserName"] = $v["biz_user_name"];
			$result[$i]["refNumber"] = $v["ref_number"];
			$result[$i]["refType"] = $v["ref_type"];
			
			$qcBeginDT = $this->toYMD($v["begin_dt"]);
			if ($qcBeginDT && $qcBeginDT != "1970-01-01") {
				$result[$i]["qcBeginDT"] = $qcBeginDT;
			}
			
			$qcEndDT = $this->toYMD($v["end_dt"]);
			if ($qcEndDT && $qcEndDT != "1970-01-01") {
				$result[$i]["qcEndDT"] = $qcEndDT;
			}
			
			$expiration = $v["expiration"];
			if ($expiration > 0) {
				$result[$i]["expiration"] = $expiration;
			}
		}
		
		$sql = "select count(*) as cnt from t_inventory_detail_lot
				where warehouse_id = '%s' and goods_id = '%s' 
				and (biz_date between '%s' and '%s')";
		$data = $db->query($sql, $warehouseId, $goodsId, $dtFrom, $dtTo);
		
		return array(
				"details" => $result,
				"totalCount" => $data[0]["cnt"]
		);
	}

	public function excel($warehouseId) {
		if ($this->isNotOnline()) {
			return;
		}
		
		$db = M();
		$sql = "select name from t_warehouse where id = '%s' ";
		$data = $db->query($sql, $warehouseId);
		if (! $data) {
			return;
		}
		
		$warehouseName = $data[0]["name"];
		
		$sql = "select v.id, g.code, g.name, g.spec, v.balance_count, v.balance_price,
				v.balance_money, u.name as unit_name,
				v.begin_dt, v.end_dt, v.expiration
				from t_inventory_lot v, t_goods g, t_goods_unit u
				where v.goods_id = g.id and g.unit_id = u.id and v.warehouse_id = '%s'
					and v.balance_count > 0
				order by g.code, v.begin_dt
				";
		$data = $db->query($sql, $warehouseId);
		$result = array();
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["goodsCode"] = $v["code"];
			$result[$i]["goodsName"] = $v["name"];
			$result[$i]["goodsSpec"] = $v["spec"];
			$result[$i]["goodsCount"] = $v["balance_count"];
			$result[$i]["goodsUnit"] = $v["unit_name"];
			$result[$i]["goodsMoney"] = $v["balance_money"];
			$result[$i]["goodsPrice"] = $v["balance_price"];
			
			$qcBeginDT = $this->toYMD($v["begin_dt"]);
			if ($qcBeginDT && $qcBeginDT != "1970-01-01") {
				$result[$i]["qcBeginDT"] = $qcBeginDT;
			}
			$qcEndDT = $this->toYMD($v["end_dt"]);
			if ($qcEndDT && $qcEndDT != "1970-01-01") {
				$result[$i]["qcEndDT"] = $qcEndDT;
			}
			$expiration = $v["expiration"];
			if ($expiration > 0) {
				$result[$i]["expiration"] = $expiration;
			}
		}
		
		// 导出Excel
		$excel = new \PHPExcel();
		
		$sheet = $excel->getActiveSheet();
		if (! $sheet) {
			$sheet = $excel->createSheet();
		}
		
		$sheet->setTitle($warehouseName);
		
		$sheet->getRowDimension('1')->setRowHeight(22);
		$sheet->setCellValue("A1", $warehouseName . " - 库存数据(导出时间: " . date("Y-m-d H:i:s") . ")");
		
		$sheet->getColumnDimension('A')->setWidth(15);
		$sheet->setCellValue("A2", "商品编码");
		
		$sheet->getColumnDimension('B')->setWidth(40);
		$sheet->setCellValue("B2", "品名");
		
		$sheet->getColumnDimension('C')->setWidth(20);
		$sheet->setCellValue("C2", "规格型号");
		
		$sheet->getColumnDimension('D')->setWidth(15);
		$sheet->setCellValue("D2", "生产日期");
		
		$sheet->getColumnDimension('E')->setWidth(15);
		$sheet->setCellValue("E2", "保质期(天)");
		
		$sheet->getColumnDimension('F')->setWidth(15);
		$sheet->setCellValue("F2", "到期日期");
		
		$sheet->getColumnDimension('G')->setWidth(15);
		$sheet->setCellValue("G2", "库存数量");
		
		$sheet->getColumnDimension('H')->setWidth(10);
		$sheet->setCellValue("H2", "单位");
		
		$sheet->getColumnDimension('I')->setWidth(15);
		$sheet->setCellValue("I2", "存货金额");
		
		$sheet->getColumnDimension('J')->setWidth(15);
		$sheet->setCellValue("J2", "存货单价");
		
		foreach ( $result as $i => $v ) {
			$row = $i + 3;
			$sheet->setCellValue("A" . $row, $v["goodsCode"]);
			$sheet->setCellValue("B" . $row, $v["goodsName"]);
			$sheet->setCellValue("C" . $row, $v["goodsSpec"]);
			$sheet->setCellValue("D" . $row, $v["qcBeginDT"]);
			$sheet->setCellValue("E" . $row, $v["expiration"]);
			$sheet->setCellValue("F" . $row, $v["qcEndDT"]);
			$sheet->setCellValue("G" . $row, $v["goodsCount"]);
			$sheet->setCellValue("H" . $row, $v["goodsUnit"]);
			$sheet->setCellValue("I" . $row, $v["goodsMoney"]);
			$sheet->setCellValue("J" . $row, $v["goodsPrice"]);
		}
		
		// 画表格边框
		$styleArray = array(
				'borders' => array(
						'allborders' => array(
								'style' => 'thin'
						)
				)
		);
		$lastRow = count($result) + 2;
		$sheet->getStyle('A2:J' . $lastRow)->applyFromArray($styleArray);
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="' . $warehouseName . ' - 库存数据.xls"');
		header('Cache-Control: max-age=0');
		
		$writer = \PHPExcel_IOFactory::createWriter($excel, "Excel5");
		$writer->save("php://output");
	}
}